﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using ExcelLoader;
using MDSAdministration.Annotations;
using OfficeOpenXml;

namespace MDSAdministration
{
    /// <summary>
    /// Interaction logic for ExcelTemplateGen.xaml
    /// </summary>
    public partial class ExcelTemplateGen : Window
    {
        public ExcelTemplateGen()
        {
            InitializeComponent();
            this.DataContext = this;
        }

        public ExcelModel eModel { get; set; }
        public CompareModel modelA { get; set; }
        public CompareModel modelB { get; set; }
        public ServerMDSDetails ServerA { get; set; }
        public ServerMDSDetails ServerB { get; set; }

        private void btnGenerate_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                GenerateExcelFile(eModel, txtFileName.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error In generating file", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                Console.WriteLine("InnerException:");
                Console.WriteLine(ex.Message);
                Console.WriteLine("Message:");
                Console.WriteLine(ex.Message);

            }
            
        }

        private string AddColumn(string columns, string columnName, ExcelAttribute a)
        {
            if (columns.Length > 0)
            {
                columns += ",";
            }

            if (a == null)
            {
                columns += "CAST(NULL AS NVARCHAR(4000)) AS '" + columnName + "'" + Environment.NewLine;
            }
            else
            {
                if (a.compAtt.isDomainAttribute)
                {
                    columns += "CAST(" + columnName + "_Code AS NVARCHAR(4000)) AS '" + columnName + "'" + Environment.NewLine;
                }
                else
                {
                    columns += "CAST(" + columnName + " AS NVARCHAR(4000)) AS '" + columnName + "'" + Environment.NewLine;
                }
                
            }
            

            return columns;
        }

        private void GenerateExcelFile(ExcelModel eModel, string fullFileName)
        {
            if (String.IsNullOrEmpty(fullFileName))
            {
                MessageBox.Show("Filename is empty", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }

            if (eModel != null)
            {

                FileInfo newFile = new FileInfo(fullFileName);
                if (newFile.Exists)
                {
                    newFile.Delete(); // ensures we create a new workbook
                    newFile = new FileInfo(fullFileName);

                   
                }

                using (ExcelPackage package = new ExcelPackage(newFile))
                {
                    foreach (var en in eModel.ExcelEntities)
                    {
                        string sqlColumns = "";

                        if (en.isChecked)
                        {
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(en.Name);

                            int index = 1;

                            if (en.isAddBKChecked == true)
                            {
                                worksheet.Column(index).Style.Numberformat.Format = "@";

                                var cell = worksheet.Cells[1, index];
                                string text =
                                    "If the entity auto generates the code value, use the Business Key as a way to reference between source system and MDS.";

                                if (cell.Comment == null)
                                {
                                    cell.AddComment(text, "MDS Administration Tool");
                                }
                                else
                                {
                                    cell.Comment.Text = text.Replace("<", "").Replace(">", "");
                                }
                                cell.Comment.AutoFit = true;

                                cell.Value = "BK";

                                sqlColumns = AddColumn(sqlColumns, "BK", null);

                                index++;
                            }

                            foreach (var a in en.ExcelAttributes)
                            {

                                worksheet.Column(index).Style.Numberformat.Format = "@";
                                var cell = worksheet.Cells[1, index];

                                string text = "";

                                if (a.compAtt.isDomainAttribute)
                                {
                                    text += Environment.NewLine + "Is Domain Attrribute.";
                                    text += Environment.NewLine + "Domain Entity Name: " + a.compAtt.DomainEntityName;
                                }
                                else
                                {
                                    switch (a.compAtt.DataTypeID)
                                    {
                                        case Constants.DATA_TYPES_DATETIME:
                                            text += Environment.NewLine + "Data Type: DateTime";
                                            break;
                                        case Constants.DATA_TYPES_LINK:
                                            text += Environment.NewLine + "Data Type: Link";
                                            break;
                                        case Constants.DATA_TYPES_NUMBER:
                                            text += Environment.NewLine + "Data Type: Number";
                                            if (a.compAtt.DataTypeInformation > 0)
                                            {
                                                text += Environment.NewLine + "Decimal Precision: " + a.compAtt.DataTypeInformation;
                                            }
                                            break;
                                        case Constants.DATA_TYPES_TEXT:
                                            text += Environment.NewLine + "Data Type: Text";
                                            if (a.compAtt.DataTypeInformation > 0)
                                            {
                                                text += Environment.NewLine + "Text Length: " + a.compAtt.DataTypeInformation;
                                            }
                                            break;

                                    }
                                }

                                if (cell.Comment == null)
                                {
                                    cell.AddComment(text, "MDS Administration Tool");
                                }
                                else
                                {
                                    cell.Comment.Text = text.Replace("<", "").Replace(">", "");
                                }
                                cell.Comment.AutoFit = true;

                                cell.Value = a.Name;

                                sqlColumns = AddColumn(sqlColumns, a.Name, a);

                                index++;
                            }

                            if (cbxDataExtract.IsChecked.HasValue && cbxDataExtract.IsChecked.Value == true)
                            {
                                string sqlSelect = "SELECT " + sqlColumns + " FROM mdm." + eModel.Name + "_" + en.Name;
                                DataSet ds = DataHelper.GetData(sqlSelect, getServer());
                                
                                if (ds.Tables.Count > 0)
                                {
                                    ExcelHelper.PopulateSheet(worksheet, ds.Tables[0], false);
                                }
                            }
                        }
                    }

                    package.Save();

                }
                MessageBox.Show("Excel File Generated", "Complete");
            }
        }

        private void btnFileSaveDialog_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();

            dlg.DefaultExt = ".xlsx"; // Default file extension
            dlg.Filter = "Excel Workbook (.xlsx)|*.xlsx"; // Filter files by extension

            // Show save file dialog box
            Nullable<bool> result = dlg.ShowDialog();

            // Process save file dialog box results
            if (result == true)
            {
                // Save document
                txtFileName.Text = dlg.FileName;
            }
        }

        private void rbServerA_Checked(object sender, RoutedEventArgs e)
        {
            CreateModel(modelA);
            CreateTree();
        }

        private ServerMDSDetails getServer()
        {
            if (rbServerA.IsChecked.Value)
            {
                return ServerA;
            }
            else
            {
                return ServerB;
            }
        }

        private void CreateModel(CompareModel model)
        {
            if (model != null)
            {
                eModel = new ExcelModel();

                eModel.Name = model.Name;
                eModel.isChecked = true;

                foreach (var en in model.Entities)
                {
                    var ee = new ExcelEntity();
                    ee.Name = en.Value.Name;
                    ee.isChecked = true;

                    eModel.ExcelEntities.Add(ee);

                    foreach (var a in en.Value.Attributes)
                    {
                        if (a.Value.IsCode == true || a.Value.IsName == true || a.Value.IsSystem == false)
                        {
                            var ea = new ExcelAttribute();
                            ea.compAtt = a.Value;
                            ea.Name = a.Value.Name;
                            ea.isChecked = true;
                            ee.ExcelAttributes.Add(ea);
                        }
                    }
                }
            }

        }

        private void rbServerB_Checked(object sender, RoutedEventArgs e)
        {
            CreateModel(modelB);
            CreateTree();
        }

        private void CreateTree()
        {
            trvModel.Items.Clear();

            if (eModel != null)
            {
                var tviModel = CreateModel(trvModel, eModel);

                foreach (var ee in eModel.ExcelEntities)
                {
                    var tviEntity = CreateEntity(tviModel, ee);
                    foreach (var ea in ee.ExcelAttributes)
                    {
                        CreateAttribute(tviEntity, ea);
                    }
                }
            }
            //trvModel.DataContext = eModel;
        }


        TreeViewItem CreateModel(TreeView trv, ExcelModel em)
        {
            TreeViewItemModel itemModel = new TreeViewItemModel();
            itemModel.Model = em;
            itemModel.KeyDown += ItemModel_OnKeyDown;
            StackPanel panel = new StackPanel();
            CheckBox checkBox = new CheckBox();
            checkBox.Focusable = false;

            Binding bc = new Binding("isChecked");
            bc.Source = em;

            checkBox.SetBinding(CheckBox.IsCheckedProperty, bc);

            TextBlock text = new TextBlock();

            panel.Orientation = Orientation.Horizontal;
            checkBox.Margin = new Thickness(5, 0, 10, 2);
            text.Text = em.Name;

            panel.Children.Add(checkBox);
            panel.Children.Add(text);

            itemModel.Header = panel;

            trv.Items.Add(itemModel);
            return itemModel;
        }

        private void ItemModel_OnKeyDown(object sender, KeyEventArgs keyEventArgs)
        {
            if (keyEventArgs.Key == Key.Space)
            {
                ((TreeViewItemModel)sender).Model.isChecked = !((TreeViewItemModel)sender).Model.isChecked;
                keyEventArgs.Handled = true;
            }
        }

        private void ItemEntity_OnKeyDown(object sender, KeyEventArgs keyEventArgs)
        {
            if (keyEventArgs.Key == Key.Space)
            {
                ((TreeViewItemEntity)sender).Entity.isChecked = !((TreeViewItemEntity)sender).Entity.isChecked;
                keyEventArgs.Handled = true;
            }
        }

        private void ItemAttributeOnKeyDown(object sender, KeyEventArgs keyEventArgs)
        {
            if (keyEventArgs.Key == Key.Space)
            {
                ((TreeViewItemAttribute)sender).Attribute.isChecked = !((TreeViewItemAttribute)sender).Attribute.isChecked;
                keyEventArgs.Handled = true;
            }
        }

        TreeViewItem CreateEntity(TreeViewItem tviModel, ExcelEntity ee)
        {
            TreeViewItemEntity itemEntity = new TreeViewItemEntity();
            itemEntity.Entity = ee;
            itemEntity.KeyDown += ItemEntity_OnKeyDown;
            StackPanel panel = new StackPanel();

            CheckBox checkBox = new CheckBox();
            checkBox.Focusable = false;

            Binding bc = new Binding("isChecked");
            bc.Source = ee;
            checkBox.SetBinding(CheckBox.IsCheckedProperty, bc);

            checkBox.Margin = new Thickness(5, 0, 0, 0);

            CheckBox checkAddBKBox = new CheckBox();
            checkAddBKBox.Focusable = false;

            Binding bca = new Binding("isAddBKChecked");
            bca.Source = ee;
            checkAddBKBox.SetBinding(CheckBox.IsCheckedProperty, bca);



            checkAddBKBox.ToolTip = "Add or remove BK column. Used to help load data into MDS by linking the sheets together.";
            checkAddBKBox.Name = "chkAddBK";
            checkAddBKBox.Margin = new Thickness(5, 0, 0, 0);



            TextBlock text = new TextBlock();
            text.Margin = new Thickness(5, -1, 0, 0);
            panel.Orientation = Orientation.Horizontal;


            text.Text = ee.Name + ", Add BK " ;

            panel.Children.Add(checkBox);
            panel.Children.Add(checkAddBKBox);
            panel.Children.Add(text);

            itemEntity.Header = panel;

            tviModel.Items.Add(itemEntity);
            return itemEntity;
        }

        void CreateAttribute(TreeViewItem tviModel, ExcelAttribute ea)
        {
            TreeViewItem itemAttribute = new TreeViewItem();
            itemAttribute.KeyDown += ItemAttributeOnKeyDown;
            StackPanel panel = new StackPanel();
            CheckBox checkBox = new CheckBox();
            checkBox.Focusable = false;

            Binding bc = new Binding("isChecked");
            bc.Source = ea;
            checkBox.SetBinding(CheckBox.IsCheckedProperty, bc);

            TextBlock text = new TextBlock();

            panel.Orientation = Orientation.Horizontal;
            checkBox.Margin = new Thickness(5, 0, 10, 2);

            text.Text = ea.Name;

            panel.Children.Add(checkBox);
            panel.Children.Add(text);

            itemAttribute.Header = panel;

            tviModel.Items.Add(itemAttribute);
        }
    }

    public class TreeViewItemModel : TreeViewItem
    {
        public ExcelModel Model { get; set; }
    }

    public class TreeViewItemEntity : TreeViewItem
    {
        public ExcelEntity Entity { get; set; }
    }

    public class TreeViewItemAttribute : TreeViewItem
    {
        public ExcelAttribute Attribute { get; set; }
    }
    public class ExcelModel : INotifyPropertyChanged
    {
        public ExcelModel()
        {
            ExcelEntities = new List<ExcelEntity>();
        }
        public string Name { get; set; }
        private bool _isChecked;
        public bool isChecked
        {
            get { return _isChecked; }
            set
            {
                _isChecked = value;
                NotifyPropertyChanged("isChecked");
            }
        }
        public List<ExcelEntity> ExcelEntities = new List<ExcelEntity>();


        public void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }

        public event PropertyChangedEventHandler PropertyChanged;

        [NotifyPropertyChangedInvocator]
        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
        }
    }

    public class ExcelEntity : INotifyPropertyChanged
    {
        public ExcelEntity()
        {
            ExcelAttributes = new List<ExcelAttribute>();
        }
        public string Name { get; set; }
        private bool _isChecked;
        public bool isChecked
        {
            get { return _isChecked; }
            set
            {
                _isChecked = value;
                NotifyPropertyChanged("isChecked");
            }
        }
        public bool isAddBKChecked { get; set; }
        public List<ExcelAttribute> ExcelAttributes = new List<ExcelAttribute>();
        public void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }

        public event PropertyChangedEventHandler PropertyChanged;

        [NotifyPropertyChangedInvocator]
        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
        }
    }

    public class ExcelAttribute : INotifyPropertyChanged
    {
        public string Name { get; set; }
        public CompareAttribute compAtt { get; set; }
        private bool _isChecked;
        public bool isChecked
        {
            get { return _isChecked; }
            set
            {
                _isChecked = value;
                NotifyPropertyChanged("isChecked");
            }
        }

        public void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }

        public event PropertyChangedEventHandler PropertyChanged;

        [NotifyPropertyChangedInvocator]
        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}
